﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using System.Configuration;
using MySql.Data.MySqlClient;

namespace SeCompUFSCarWeb.Models
{
    public class SignUp
    {
        //Dados de Conexão
        string strconn = ConfigurationManager.ConnectionStrings["DefaultConnection"].ConnectionString;

        int id;
        int pack;
        int courseID;
        int total;
        string shirt;
        string userID;
        bool payment;
        bool marathon;
        bool marketing;
        bool barbecue;
        DateTime date;

        

        #region Constructors
        public SignUp() { }

        public SignUp(int pack, int courseID, int total, string shirt, string userID, bool payment, bool marathon, bool marketing, bool barbecue, DateTime date) 
        {
            this.pack = pack;
            this.courseID = courseID;
            this.total = total;
            this.shirt = shirt;
            this.userID = userID;
            this.payment = payment;
            this.marathon = marathon;
            this.marketing = marketing;
            this.barbecue = barbecue;
            this.date = date;
        }

        public SignUp(string userID)
        {
            this.userID = userID;
        }
        #endregion

        #region Getters and Setters
        public int Id
        {
            get { return id; }
            set { id = value; }
        }

        public int Pack
        {
            get { return pack; }
            set { pack = value; }
        }

        public int CourseID
        {
            get { return courseID; }
            set { courseID = value; }
        }

        public int Total
        {
            get { return total; }
            set { total = value; }
        }

        public string UserID
        {
            get { return userID; }
            set { userID = value; }
        }

        public string Shirt
        {
            get { return shirt; }
            set { shirt = value; }
        }

        public bool Barbecue
        {
            get { return barbecue; }
            set { barbecue = value; }
        }

        public bool Marketing
        {
            get { return marketing; }
            set { marketing = value; }
        }

        public bool Marathon
        {
            get { return marathon; }
            set { marathon = value; }
        }

        public bool Payment
        {
            get { return payment; }
            set { payment = value; }
        }

        public DateTime Date
        {
            get { return date; }
            set { date = value; }
        }  
        #endregion      

        public bool Insert()
        {
            //Utilizando a conexão
            MySqlConnection conn = new MySqlConnection(strconn);

            string query = "INSERT INTO secomp.signup (user_id, shirt, course_id, payment, date, pack, barbecue, marketing, marathon, total) VALUES (@userID, @shirt, @courseID, @payment, @date, @pack, @barbecue, @marketing, @marathon, @total)";
                
            MySqlCommand cmd = new  MySqlCommand(query, conn);
            cmd.Parameters.AddWithValue("@userID", userID);
            cmd.Parameters.AddWithValue("@shirt", shirt);
            cmd.Parameters.AddWithValue("@courseID", courseID);
            cmd.Parameters.AddWithValue("@payment", payment);
            cmd.Parameters.AddWithValue("@date", date.ToString("yyyy-MM-dd HH:mm:ss"));
            cmd.Parameters.AddWithValue("@pack", pack);
            cmd.Parameters.AddWithValue("@barbecue", barbecue);
            cmd.Parameters.AddWithValue("@marketing", marketing);
            cmd.Parameters.AddWithValue("@marathon", marathon);
            if (pack == 0)
                cmd.Parameters.AddWithValue("@total", 1);
            else
                cmd.Parameters.AddWithValue("@total", 0);


            try
            {
                conn.Open();
                cmd.ExecuteNonQuery();
            }
            catch (Exception)
            {
                return false;
            }
            finally
            {
                conn.Close();
            }

            return true;
           
        }

        public int NumeroInscricao()
        {
            int inscricao = 0;
            //Utilizando a conexão
            MySqlConnection conn = new MySqlConnection(strconn);

            //INSERT Query
            string query = "SELECT id FROM secomp.signup WHERE user_id ='" + userID + "'";
            MySqlCommand cmd = new MySqlCommand(query, conn);

            try
            {
                conn.Open();
                MySqlDataReader reader = cmd.ExecuteReader();

                while (reader.Read())
                {
                    inscricao = (int)reader["id"];
                }
            }
            catch (Exception)
            {
                return inscricao = 0;
            }
            finally
            {
                conn.Close();
            }

            return inscricao;
        }

        public void VerificaInscricaoByUserID()
        {
            //Utilizando a conexão
            MySqlConnection conn = new MySqlConnection(strconn);

            //INSERT Query
            string query = "SELECT * FROM secomp.signup WHERE user_id = @userID";
            MySqlCommand cmd = new MySqlCommand(query, conn);

            cmd.Parameters.AddWithValue("@userID", userID);

            try
            {
                conn.Open();
                MySqlDataReader reader = cmd.ExecuteReader();

                while (reader.Read())
                {
                    this.id = reader.GetInt32("id");
                    this.payment = reader.GetBoolean("payment");
                    this.pack = (int)reader["pack"];
                    this.courseID = (int)reader["course_id"];
                   
                }
            }
            catch (Exception e)
            {
                string ex = e.ToString();
            }
            finally
            {
                conn.Close();
            }

        }

        public bool VerificaInscricaoByID()
        {
            //Utilizando a conexão
            MySqlConnection conn = new MySqlConnection(strconn);
            bool find = false;

            //INSERT Query
            string query = "SELECT * FROM secomp.signup WHERE id = @ID";
            MySqlCommand cmd = new MySqlCommand(query, conn);

            cmd.Parameters.AddWithValue("@ID", this.id);

            try
            {
                conn.Open();
                MySqlDataReader reader = cmd.ExecuteReader();

                while (reader.Read())
                {
                    find = true;
                    this.id = reader.GetInt32("id");
                    this.userID = reader.GetString("user_id");
                    this.pack = reader.GetInt32("pack");
                    this.barbecue = reader.GetBoolean("barbecue");
                    this.total = reader.GetInt32("total");
                    this.payment = reader.GetBoolean("payment");
                }
            }
            catch (Exception e)
            {
                return false;
            }
            finally
            {
                conn.Close();
            }

            return find;
        }

        public bool EfetuarPagamento()
        {
            MySqlConnection conn = new MySqlConnection(strconn);

            string query = "UPDATE secomp.signup SET payment = @payment, barbecue = @barbecue, total = @total WHERE id = @id";

            MySqlCommand cmd = new MySqlCommand(query, conn);
            cmd.Parameters.AddWithValue("@id", this.id);
            cmd.Parameters.AddWithValue("@payment", payment);
            cmd.Parameters.AddWithValue("@barbecue", barbecue);
            cmd.Parameters.AddWithValue("@total", 1);

            try
            {
                conn.Open();
                cmd.ExecuteNonQuery();
            }
            catch (Exception)
            {
                return false;
            }
            finally
            {
                conn.Close();
            }

            return true;
        }
    }
}